package com.wht.mapper;

import com.wht.vo.CategorySellAmts;
import com.wht.vo.DayStatis;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wanghaotian
 * time:2022/12/05,0005 11:04
 * description:
 */

@SuppressWarnings("rawtypes")
public interface StatMapper {
    @Select("select DATE_FORMAT(t.add_time,'%Y-%m-%d') as dayStr,COUNT(1) as cnts\n" +
            "FROM dts_user t\n" +
            "WHERE t.add_time > DATE_ADD(NOW(),INTERVAL - #{daysAgo} DAY) AND t.deleted=0\n" +
            "GROUP BY DATE_FORMAT(t.add_time,'%Y-%m-%d')")
    public List<DayStatis> statisIncreaseUserCnt(@Param("daysAgo") int daysAgo);

    @Select("select DATE_FORMAT(t.add_time,'%Y-%m-%d') as dayStr,COUNT(1) as cnts,SUM(actual_price) as amts\n" +
            "FROM dts_order t\n" +
            "WHERE t.add_time > DATE_ADD(NOW(),INTERVAL - #{daysAgo} DAY) AND t.deleted=0\n" +
            "GROUP BY DATE_FORMAT(t.add_time,'%Y-%m-%d')")
    public List<DayStatis> statisIncreaseOrderCnt(@Param("daysAgo") int daysAgo);

    @Select("SELECT pc.name as name,SUM(og.price) as value\n" +
            "FROM dts_order_goods og\n" +
            "JOIN dts_order o ON o.id=og.order_id\n" +
            "JOIN dts_goods g ON g.id=og.goods_id\n" +
            "JOIN dts_category c ON c.id=g.category_id\n" +
            "JOIN dts_category pc ON pc.id=c.pid\n" +
            "WHERE og.deleted=0 AND o.order_status NOT IN(101,102,103)\n" +
            "GROUP BY pc.name")
    public List<CategorySellAmts> categorySellStatis();
}
